﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace T4Helper
{
    public class ModelHelper
    {
        static void Main()
        {
            SqlHelper sql = new SqlHelper();

            string getAlldt = string.Format("use NFineBase;select  name as TableName from sysobjects  where xtype='U'");
          DataTable dt=  sql.ReturnDataTable(getAlldt);

           
          foreach (DataRow row in dt.Rows)
          {
              string dtName = row["TableName"].ToString();

              string getAllColumnStr = string.Format(@"select tableColumn.name as 字段名称, ctype.name as 数据库字段类型,tableColumn.length as 字段长度 from SysColumns as tableColumn
inner join SysObjects as sobj on tableColumn.id=sobj.id
inner join systypes as  ctype on tableColumn.xtype=ctype.xtype
where sobj.xtype='U'
and sobj.name='{0}'", dtName);

              DataTable dtColunm=sql.ReturnDataTable(getAllColumnStr);


              foreach (DataRow columnrow in dtColunm.Rows)
              {
                  string columnName = columnrow["ColumnName"].ToString();

                  string TypeName = columnrow["TypeName"].ToString();

                  string length = columnrow["length"].ToString();

              }

          }


        }
       

       
    }


    public class SqlHelper
    {

        public SqlHelper(string constr)
        {
            ConnectionStr = constr;
        }
        public SqlHelper()
        {
                
        }

        private string ConnectionStr = @"data source=.;database=NFineBase;user id=sa;password=1;";

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable  ReturnDataTable(string sql)
        {

            DataTable dt=new DataTable();
            SqlConnection con=new SqlConnection() ;
            SqlCommand cmd = new SqlCommand(); ;
            SqlDataAdapter adapter = new SqlDataAdapter(); ;
            try
            {
                con.ConnectionString = ConnectionStr;
                con.Open();
                 cmd = con.CreateCommand();
                 cmd.CommandText = sql;
                 cmd.CommandType = CommandType.Text;

                 adapter.SelectCommand = cmd;

                adapter.Fill(dt);

                
              
            }
            catch
            { }
            finally
            {
               
                con.Dispose();
                cmd.Dispose();
                adapter.Dispose();
               
            }

            return dt;
        
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public  int ExecuteNonQuery(string sql, params SqlParameter[] paramList)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionStr))
            {
                conn.Open();
                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(paramList);
                    return command.ExecuteNonQuery();
                }
            }
        }


        public  object ExecuteScalar(string sql, params SqlParameter[] paramList)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionStr))
            {
                conn.Open();
                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(paramList);
                    return command.ExecuteScalar();
                }
            }
        }
    }
}
